package org.elanfox.cloud.utils;

import lombok.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by allan on 3/24/16.
 */
public class ExcelUtils {
    public static HSSFWorkbook createWorkbook(List<Sheet> sheets) {
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (Sheet sheet : sheets) {
            createSheet(workbook, sheet);
        }
        return workbook;
    }

    public static HSSFWorkbook createWorkbook(Sheet... sheets) {
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (Sheet sheet : sheets) {
            createSheet(workbook, sheet);
        }
        return workbook;
    }

    private static void createSheet(HSSFWorkbook workbook, Sheet sheet) {
        HSSFSheet hssfSheet = workbook.createSheet(sheet.getName());
        //创建表头
        HSSFRow thRow = hssfSheet.createRow(0);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 背景色
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        // 设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont hssfFont = workbook.createFont();
        hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        hssfFont.setFontHeightInPoints((short) 14);
        cellStyle.setFont(hssfFont);
        thRow.setHeightInPoints(20);

        for (int i = 0; i < sheet.th.length; i++) {
            HSSFCell hssfCell = thRow.createCell(i);
            hssfCell.setCellStyle(cellStyle);
            hssfCell.setCellValue(sheet.th[i]);
            hssfSheet.autoSizeColumn(i);
        }
        //创建表单
        for (int i = 0; i < sheet.rows.size(); i++) {
            HSSFRow row = hssfSheet.createRow(i + 1);
            for (int j = 0; j < sheet.rows.get(i).size(); j++) {
                CellDesc cellDesc = sheet.rows.get(i).get(j);
                HSSFCell cell = row.createCell(j);
                cell.setCellType(cellDesc.cellType);
                if (cellDesc.cellType == Cell.CELL_TYPE_NUMERIC) {
                    if (cellDesc.getValue() != null) {
                        cell.setCellValue(new Double(String.valueOf(cellDesc.getValue())));
                    } else {
                        cell.setCellValue((double) 0);
                    }
                } else {
                    cell.setCellValue(String.valueOf(cellDesc.getValue()));
                }
            }
        }
    }

    /**
     * 创建一个excel文档
     *
     * @param sheetName   sheet的名称
     * @param thCells     标题
     * @param rowAndCells 行和列信息 {@link CellDesc}
     * @return
     */
    public static HSSFWorkbook createWorkbook(
            String sheetName,
            String[] thCells,
            List<List<CellDesc>> rowAndCells
    ) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //创建表头
        HSSFRow thRow = sheet.createRow(0);
        for (int i = 0; i < thCells.length; i++) {
            thRow.createCell(i).setCellValue(thCells[i]);
        }
        //创建表单
        for (int i = 0; i < rowAndCells.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < rowAndCells.get(i).size(); j++) {
                CellDesc cellDesc = rowAndCells.get(i).get(j);
                HSSFCell cell = row.createCell(j);
                cell.setCellType(cellDesc.cellType);
                if (cellDesc.cellType == Cell.CELL_TYPE_NUMERIC) {
                    if (cellDesc.getValue() != null) {
                        cell.setCellValue(new Double(String.valueOf(cellDesc.getValue())));
                    } else {
                        cell.setCellValue((double) 0);
                    }
                } else {
                    cell.setCellValue(String.valueOf(cellDesc.getValue()));
                }
            }
        }
        return workbook;
    }

    /**
     * 读取excel文档
     *
     * @param workbook
     * @param rowStartWith 起始行数
     * @param cellNum      总列数
     * @return
     */
    public static List<List<CellDesc>> readWorkbook(
            HSSFWorkbook workbook,
            int rowStartWith,
            int cellNum
    ) {
        List<List<CellDesc>> rowAndCells = new ArrayList<>();
        HSSFSheet sheet = workbook.getSheetAt(0);
        int physicalRowNum = sheet.getPhysicalNumberOfRows();
        for (int i = rowStartWith; i < physicalRowNum; i++) {
            List<CellDesc> cellDescList = new ArrayList<>();
            HSSFRow row = sheet.getRow(i);
            for (int j = 0; j < cellNum; j++) {
                HSSFCell cell = row.getCell(j);
                Object value = cell == null ? null : getValue(cell);
                cellDescList.add(asCell(value));
            }
            rowAndCells.add(cellDescList);
        }
        return rowAndCells;
    }

    /**
     * 读取excel文档
     *
     * @param workbook
     * @param sheetName
     * @param rowStartWith  起始行数
     * @param cellStartWith 起始列数
     * @return
     */
    public static List<List<CellDesc>> readWorkbook(
            HSSFWorkbook workbook,
            String sheetName,
            int rowStartWith,
            int cellNum,
            int cellStartWith
    ) {
        List<List<CellDesc>> rowAndCells = new ArrayList<>();
        HSSFSheet sheet = workbook.getSheet(sheetName);
        int physicalRowNum = sheet.getPhysicalNumberOfRows();
        for (int i = rowStartWith; i < physicalRowNum; i++) {
            List<CellDesc> cellDescList = new ArrayList<>();
            HSSFRow row = sheet.getRow(i);
            for (int j = cellStartWith; j < cellNum; j++) {
                HSSFCell cell = row.getCell(j);
                Object value = cell == null ? null : getValue(cell);
                cellDescList.add(asCell(value));
            }
            rowAndCells.add(cellDescList);
        }
        return rowAndCells;
    }

    /**
     * 得到Excel表中的值
     *
     * @param hssfCell Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    public static Object getValue(Cell hssfCell) {
        if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return hssfCell.getBooleanCellValue();
        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return hssfCell.getNumericCellValue();
        } else {
            // 返回字符串类型的值
            return hssfCell.getStringCellValue();
        }

    }


    public static CellDesc asCell(Object value, int cellType) {
        return new CellDesc(value, cellType);
    }

    public static CellDesc asCell(Object value) {
        if (value == null) {
            value = "";
        }
        return new CellDesc(value, Cell.CELL_TYPE_STRING);
    }

    @Setter
    @Getter
    @AllArgsConstructor
    public static class CellDesc {
        private Object value;
        private int cellType;

        public String getString() {
            if (value == null) {
                return null;
            }
            return String.valueOf(value);
        }

        public Integer getInt() {
            if (value == null) {
                return null;
            }
            return Double.valueOf(String.valueOf(value)).intValue();
        }
    }

    @Data
    @Builder
    public static class Sheet {
        private String name;
        private String[] th;
        private List<List<CellDesc>> rows;
    }
}
